#!/usr/bin/python
# -*- coding: utf-8 -*-  

"""
@Project : hello 
@file : xx_schedule_job_rerun.py
@Author : sheen
@time : 2025/4/10 12:55
@func : xx_schedule_job 重跑工具脚本



"""
import time
from com.cn.for_cdc.common.cdc_conmons import mysql_pro_connections
from mysql_demo import MySQLHelper



def gener_cron_text():
    from datetime import datetime, timedelta
    # 计算未来30秒后的时间
    future_time = datetime.now() + timedelta(seconds=10) + timedelta(hours=-8)
    cron_seconds = future_time.second
    cron_minutes = future_time.minute
    cron_hours = future_time.hour
    cron_expression = f"{cron_seconds} {cron_minutes} {cron_hours} * * ?"
    return cron_expression


def rerun(db, bu, job_id, force=False):
    cron = gener_cron_text()
    rerun_job_sql = f"update asw_etl_{bu}_job.XX_SCHEDULE_JOB set STATUS=1, RESTART=1,CRON='{cron}'  where job_id = {job_id} ;"

    # 直接重启任务
    if force:
        print(f"bu:{bu} 的job_id:{job_id} 直接重启任务。重启SQl:\n{rerun_job_sql}")
        db.execute(rerun_job_sql)
    else:
        # 重启任务前先判断最新的任务是否是失败的
        check_sql = f"SELECT STATUS FROM asw_etl_{bu}_job.XX_JOB_LOG where job_id= {job_id} order by created_time desc LIMIT 1 ; "
        print('重启任务前先判断最新的任务是否是失败的:\n'+check_sql)
        res = db.query(check_sql)
        if res[0]['STATUS'] == 'F':
            print(f"bu:{bu} 的job_id:{job_id} 失败,设置重跑标识。重启SQl:\n{rerun_job_sql}")
            db.execute(rerun_job_sql)
        else:
            print(f"bu:{bu} 的job_id:{job_id}状态不是F,不设置重跑标识。")


def close_rerun(db, bu, job_id, force=False):
    print('等待30秒，查看任务是否重启...')
    time.sleep(30)

    close_rerun_sql = f"update asw_etl_{bu}_job.XX_SCHEDULE_JOB set STATUS=0 and CRON='{cron}' where job_id ={job_id} ;"
    # close_rerun_sql = f"update asw_etl_{bu}_job.XX_SCHEDULE_JOB set STATUS=0 where job_id ={job_id} ;"


    if force:
        print(f"bu:{bu}的job_id:{job_id} 强制关闭重跑标识,SQl:\n{close_rerun_sql}")
        db.execute(close_rerun_sql)
    else:
        # 隔1分钟检测一次，任务是否成功
        while True:
            check_sql = f"SELECT STATUS FROM asw_etl_{bu}_job.XX_JOB_LOG where job_id= {job_id} order by created_time desc LIMIT 1 ; "
            res = db.query(check_sql)
            if res[0]['STATUS'] == 'S':
                print(f"bu:{bu}的job_id:{job_id} 已重跑成功,关闭重跑标识。SQl:\n{close_rerun_sql}")
                db.execute(close_rerun_sql)
                break
            elif res[0]['STATUS'] == 'R':
                print(f"bu:{bu}的job_id:{job_id} 已开始重跑,关闭重跑标识。SQl:\n{close_rerun_sql}")
                db.execute(close_rerun_sql)
                break
            else:
                print(f"bu:{bu} 的job_id:{job_id} 失败,还未关闭重跑标识")
                time.sleep(60)


def check_rerun(bu, jobs):
    db_configs = mysql_pro_connections[f'{bu}_pro_job']
    db = MySQLHelper(**db_configs)
    for job_id in jobs:
        task={"bu": bu, "job_id": job_id}
        # 隔1分钟检测一次，任务是否成功
        while True:
            check_sql = f"SELECT STATUS FROM asw_etl_{bu}_job.XX_JOB_LOG where job_id= {job_id} order by created_time desc LIMIT 1 "
            print(f'检查bu:{bu}的job_id:{job_id}任务是否重启成功:\n'+check_sql)
            res = db.query(check_sql)
            if res[0]['STATUS'] == 'S':
                print(f"{task} 已重跑成功。")
                db.execute(check_sql)
                break
            else:
                print(f"bu:{bu} 的job_id:{job_id} 还未重跑成功...")
                time.sleep(60)


def batch_rerun(bu, jobs, force=False):
    db_configs = mysql_pro_connections[f'{bu}_pro_job']
    # print(db_configs)
    db = MySQLHelper(**db_configs)

    # 批量设置重跑标识
    for job_id in jobs:
        rerun(db, bu, job_id, force)

    # 批量关闭重跑标识
    for job_id in jobs:
        close_rerun(db, bu, job_id, force)




def ckeck_all_job(bu):
    check_sql = f"""
                select
                    job_id
                from
                    (
                    select
                        job_id,
                        status,
                        RANK() OVER (PARTITION BY job_id
                    ORDER BY
                        created_time DESC) AS rn
                    from
                        asw_etl_{bu}_job.XX_JOB_LOG
                    where
                        DATE(created_time)>= curdate()-1 ) t
                where
                    t.rn = 1
                    and t.status = 'F' ;
            """
    db_configs = mysql_pro_connections[f'{bu}_pro_job']
    db = MySQLHelper(**db_configs)
    res = db.query(check_sql)
    job_ids = [i.get('job_id') for i in res]
    print(check_sql)
    print(job_ids)
    return job_ids


def run_job(tasks):
    for bu, jobs in tasks.items():
        # 强制重启
        # batch_rerun(bu, jobs,force=True)
        batch_rerun(bu, jobs)

        # 批量检查是否重跑成功
        check_rerun(bu, jobs)

def method_name():
    bu_list = ['kvn', 'kvb', 'wtctr', 'drlv', 'drlt', 'tps', 'icinl', 'icibe']
    for bu in bu_list:
        jobs = ckeck_all_job(bu)
    #     # batch_rerun(bu, jobs)
    # jobs=ckeck_all_job(bu)
    pass


if __name__ == '__main__':
    tasks = {
        "icibe": [70600],
        # "drlt": [1270]
    }
    run_job(tasks)
    # method_name()
    # gener_cron_text()














